<!DOCTYPE html>
<html>
	<head>
		<link type="text/css" rel="stylesheet" href="../stylesheet.css" />
		<title>Manager View</title>
		
		<script type="text/javascript">
			function tab(tab)
			{
				document.getElementById('tab1').style.display = 'none';
				document.getElementById('tab2').style.display = 'none';
				document.getElementById('li_tab1').setAttribute("class", "");
				document.getElementById('li_tab2').setAttribute("class", "");
				document.getElementById(tab).style.display = 'block';
				document.getElementById('li_'+tab).setAttribute("class", "active");
			}
		</script>
	</head>
	<?php
		$username = 'root';
        $pw = 'password';
		$con = mysqli_connect ("localhost", $username, $pw, 'elliphinos');
		
		if (mysqli_connect_errno()) 
		{
             echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
		
		//set time to the central time zone
		if ( !date_default_timezone_set ( 'America/Chicago' ) )
		{
			echo "could not set time zone!";
		}
		
		$date = new DateTime();
		/**sum hourly totals**/
		//create start and end times for the hours query
		$datetime_string = date_format($date, 'Y-m-d');
		$hour = date_format($date, 'H');
		$hourQueryTimeStart = $datetime_string . " $hour" .":00:00    ";
		$hour++;
		$hourQueryTimeEnd = $datetime_string . " " . $hour . ":00:00";
		
		//get hourly subtotals
		$sql = "SELECT SUM(subtotal)  AS subtotal_hourly FROM sales WHERE time BETWEEN '$hourQueryTimeStart' AND '$hourQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$subtotal_hourly = $row['subtotal_hourly'];
		if (!isset($subtotal_hourly) )
			$subtotal_hourly = 0;
			
		//get hourly total tax store as tax_hourly
		$sql = "SELECT SUM(tax)  AS tax_hourly FROM sales WHERE time BETWEEN '$hourQueryTimeStart' AND '$hourQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$tax_hourly = 0;
		$tax_hourly = $row['tax_hourly'];
		if (!isset($tax_hourly) )
			$tax_hourly = 0;
	
		//get hourly total, store as total_hourly
		$sql = "SELECT SUM(total)  AS total_hourly FROM sales WHERE time BETWEEN '$hourQueryTimeStart' AND '$hourQueryTimeEnd'";
		$result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$total_hourly = 0;
		$total_hourly = $row['total_hourly'];
		if ( !isset($total_hourly) )
			$total_hourly = 0;
			
		/**sum daily totals**/
		$dailyQueryTimeStart = $datetime_string . " 00:00:01";
		$dailyQueryTimeEnd = $datetime_string . " 23:59:59";
		
		$sql = "SELECT SUM(subtotal)  AS subtotal_daily FROM sales WHERE time BETWEEN '$dailyQueryTimeStart' AND '$dailyQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$subtotal_daily = $row['subtotal_daily'];
		if (!isset($subtotal_daily) )
			$subtotal_daily = 0;
			
		$sql = "SELECT SUM(tax)  AS tax_daily FROM sales WHERE time BETWEEN '$dailyQueryTimeStart' AND '$dailyQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$tax_daily = $row['tax_daily'];
		if (!isset($tax_daily) )
			$tax_daily = 0;
			
		//total is subtotal + tax so we can avoid doing a sql sum query for this one
		$total_daily = $subtotal_daily + $tax_daily;
		
		/**sum weekly totals**/
		$day = date_format($date, 'd');
		$month = date_format($date, 'm');
		
		if ($day > 7) 
		{
			$day = $day - 7;
		}
		else 
		{//else it is at the beginning of the month so we need to change what month it is
			$month--;
			if ($month == 0) 
			{ //if it is january we need to flip things back to being in december
				$month = 12;
			}
			switch ($month) 
			{ //this switch statement changes the day based on how many days are in in the previous month
				case 1:
					$day = 31 + $day - 7;
					break;
				case 2:
					$day = 28 + $day - 7;
					break;
				case 3:
					$day = 31 + $day - 7;
					break;
				case 4:
					$day = 30 + $day - 7;
					break;
				case 5:
					$day = 31 + $day - 7;
					break;
				case 6:
					$day = 30 + $day - 7;
					break;
				case 7:
					$day = 31 + $day - 7;
					break;
				case 8:
					$day = 31 + $day - 7;
					break;
				case 9:
					$day = 30 + $day - 7;
					break;
				case 10:
					$day = 31 + $day - 7;
					break;
				case 11:
					$day = 30 + $day - 7;
					break;
				case 12:
					$day = 31 + $day - 7;
					break;
			}
		}//end if/else for modifying month and day
		
		//now that we have correct month and day we can create our start date
		$year = date_format($date, 'Y');
		if ($month == 12) {//special case if it was between january 1st and 7th
			$year--;
		}
		$day = sprintf("%02s", $day);
		$month = sprintf("%02s", $month);
		$weeklyQueryTimeStart = $year  . "-" . $month . "-" . $day . " " . date_format($date, 'H:i:s');
		$weeklyQueryTimeEnd = date_format($date, 'Y-m-d H:i:s');
		
		$sql = "SELECT SUM(subtotal)  AS subtotal_weekly FROM sales WHERE time BETWEEN '$weeklyQueryTimeStart' AND '$weeklyQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$subtotal_weekly = $row['subtotal_weekly'];
		
		$sql = "SELECT SUM(tax)  AS tax_weekly FROM sales WHERE time BETWEEN '$weeklyQueryTimeStart' AND '$weeklyQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$tax_weekly = $row['tax_weekly'];
		
		$total_weekly = $tax_weekly + $subtotal_weekly;
		
		//echo "</br><b>WEEKLY TOTALS$weeklyQueryTimeStart     $weeklyQueryTimeEnd</b></br>subtotal: $subtotal_weekly ... tax: $tax_weekly ... $total_weekly</br>";
		
		/**sum monthly totals**/
		$monthlyQueryTimeStart = date_format($date, 'Y-m') . '-01' . date_format($date, ' 00:00:01');
		$monthlyQueryTimeEnd = date_format($date, 'Y-m-d H:i:s');
		
		$sql = "SELECT SUM(subtotal)  AS subtotal_monthly FROM sales WHERE time BETWEEN '$weeklyQueryTimeStart' AND '$weeklyQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$subtotal_monthly = $row['subtotal_monthly'];
		
		$sql = "SELECT SUM(tax)  AS tax_monthly FROM sales WHERE time BETWEEN '$monthlyQueryTimeStart' AND '$monthlyQueryTimeEnd'";
        $result = mysqli_query($con, $sql);
		$row = mysqli_fetch_array($result);
		$tax_monthly = $row['tax_monthly'];
		
		$total_monthly = $tax_monthly + $subtotal_monthly;
		
		//echo "<b>MONTHLY  $monthlyQueryTimeStart   $monthlyQueryTimeEnd </b></br>subtotal: $subtotal_monthly ... tax: $tax_monthly ... total: $total_monthly</br>"
	?>
	<body>
        <h1 class="title">Elliphino's</h1>
        <h2 class="subtitle">Manager View</h2>
		 <div id="tabs" style="top: 15%; left: 12%;">
			<ul>
				<li id="li_tab1" onclick="tab('tab1')"><a>Sales Data</a></li>
				<li id="li_tab2" onclick="tab('tab2')"><a>Inventory</a></li>
			</ul>
			<div id="Content_Area">
				<div id="tab1">
					<table border="1">
						<thead>Sales Data
							<tr>
								<th></th>
								<th>This Hour</th>
								<th>Today</th>
								<th>This Week</th>
								<th>This Month</th>
							</tr>
						</thead>
						
						<tbody>
							<tr>
								<td>Subtotal</td>
										<?php   //print out subtotal data into table cells
													  echo  "<td>" . $subtotal_hourly . "</td>";
													  echo  "<td>" . $subtotal_daily . "</td>";
													  echo  "<td>" . $subtotal_weekly . "</td>";
													  echo  "<td>" . $subtotal_monthly . "</td>\n";
										?>
							</tr>
							
							<tr>
								<td>Tax</td>
									<?php		//print out tax data into table cells
													echo  "<td>" . $tax_hourly . "</td>";
													echo  "<td>" . $tax_daily . "</td>";
													echo  "<td>" . $tax_weekly . "</td>";
													echo  "<td>" . $tax_monthly . "</td>\n";
									?>
							</tr>
							
							<tr>
								<td>Total</td>
									<?php     //print out total data into table cells
													echo  "<td>" . $total_hourly . "</td>";
													echo  "<td>" . $total_daily . "</td>";
													echo  "<td>" . $total_weekly . "</td>";
													echo  "<td>" . $total_monthly . "</td>\n";
									?>
							</tr>
						</tbody>
					</table>
				</div>
				<?php //get all values in the "item_name" column
					$sql = "SELECT item_name FROM inventory";
					$result = mysqli_query($con, $sql);
					
					$item_array = array();

					while($row = mysqli_fetch_assoc($result))
					{
						$item_array[] = $row['item_name'];
					}
					$sql2 = "SELECT quantity FROM inventory";
					$result2 = mysqli_query($con, $sql2);
					$quantity_array = array();
					while($row2 = mysqli_fetch_assoc($result2))
					{
						$quantity_array[] = $row2['quantity'];
					}	
				?>
				<div id="tab2" style="display: none;">
					<table border="1">
						<thead>Store Inventory
							<tr>
								<th>Item Type</th>
								<th>Item Quantity</th>
								<th>Quantity Units</th>
								<th>Modify Quantity</th>
							</tr>
						</thead>
						<tbody>
							<?php
								$i = 0;//need a counter for quantity_array
								//foreach loop here outputs items and their quantities gathered from the DB
								foreach ($item_array as $anItem) 
								{
									echo "<tr><td>$anItem</td><td>" . $quantity_array[$i] . "</td>";
									echo "<td>";
									switch ($i) 
									{// This *beautiful* switch statement outputs the unit associated with each type of food 
										case 0:
											echo "pounds";
											break;
										case 1:
											echo "bags";
											break;
										case 2:
											echo "pounds";
											break;
										case 3:
											echo "boxes";
											break;
										case 4:
											echo "pounds";
											break;
										case 5:
											echo "boxes";
											break;
										case 6:
											echo "bags";
											break;
										case 7:
											echo "bags";
											break;
										case 8:
											echo "loafs";
											break;
										case 9:
											echo "loafs";
											break;
										case 10:
											echo "pounds";
											break;
										case 11:
											echo "pounds";
											break;
										case 12:
											echo "servings";
											break;
										case 13:
											echo "bags";
											break;
										case 14:
											echo "pounds";
											break;
										case 15:
											echo "servings";
											break;
										case 16:
											echo "pounds";
											break;
										case 17:
											echo "bags";
											break;
										case 18:
											echo "pounds";
											break;
										case 19:
											echo "pounds";
											break;
										case 20:
											echo "pounds";
											break;
										case 21:
											echo "pounds";
											break;
										case 22:
											echo "bags";
											break;
										case 23:
											echo "pounds";
											break;
										case 24:
											echo "pounds";
											break;
										case 25:
											echo "bags";
											break;
										case 26:
											echo "bags";
											break;
										case 27:
											echo "pounds";
											break;
										case 28:
											echo "pounds";
											break;
										case 29:
											echo "tubs";
											break;
										case 30:
											echo "loafs";
											break;
										case 31:
											echo "loafs";
											break;
									}
									
									//remove whitespace in strings that have it so that html can accept it as an attribute name
									$anItemWithSpace = $anItem;
									$anItem = str_replace(' ', '', $anItem);
									
									//query for updating database when user submitted for SUBTRACTING from item count
									$name = "subtract" . $anItem;
									if ( isset($_POST[$name]) )
									{//if subtract button is pushed get the user specified quantity and update database
										$index = "quantity_change" . $anItem;
										$quantity = $_POST[$index];
										//echo "anItem:  " . $anItem . "  Quantity: " . $quantity;
										$sql = "UPDATE inventory SET quantity=(quantity - " . $quantity . ") WHERE item_name='" . $anItemWithSpace . "'";
										mysqli_query($con, $sql);
									}
									
									//query for updating database when user submitted for ADDING to item count
									$name = "add" . $anItem;
									if ( isset($_POST[$name]) )
									{//if subtract button is pushed get the user specified quantity and update database
										$index = "quantity_change" . $anItem;
										$quantity = $_POST[$index];
										//echo "anItem:  " . $anItem . "  Quantity: " . $quantity;
										$sql = "UPDATE inventory SET quantity=(quantity + " . $quantity . ") WHERE item_name='" . $anItemWithSpace . "'";
										mysqli_query($con, $sql);
									}
									/**form for modifying quantities of items**/
									echo "</td><td>";
									echo "<form method=\"POST\" action=''>";
									
									$name = "subtract" . $anItem;
									echo "<input type=\"submit\" name=\"{$name}\" value=\"-\">";//minus button
									$name = "quantity_change" . $anItem;
									echo "<input type=\"text\" name=\"{$name}\" size=\"4\" maxlength=\"2\" value=\"0\">";//amount field
									$name = "add" . $anItem;
									echo "<input type=\"submit\" name=\"{$name}\" value=\"+\">";//plus button
									echo "</form>";
									echo "</td></tr>\n";
									$i++;
								}
							?>
						</tbody>
					</table>
				</div>
			</div>
		</div>
        <a href="../index.html" id="back">Back</a>
	</body>
</html>